<?php

/**
 * AccountSettingDAO
 *
 */
class AccountTrackingDAO
{
  public function insert($mac, $tracking) {
    $db = Yii::app()->db;
    $deleteFlag = Constants::DELETE_FLAG_TRUE;

    $sql = 'INSERT INTO account_tracking ('
        . 'account_id, '
        . 'tracking_date, '
        . 'money_before, '
        . 'money_after, '
        . 'click_num, '
        . 'ad_status, '
        . 'grid_status, '
        . 'login_status, '
        . 'mac_address '
        . ') VALUES ('
        . ':accountId,'
        . 'NOW(), '
        . ':before,'
        . ':after,'
        . ':click,'
        . ':adStatus,'
        . ':gridStatus,'
        . ':loginStatus,'
        . ':macAddress'
        . ')';

    $command = $db->createCommand($sql);
    $command->bindParam(':accountId', $tracking->accountId);
    $command->bindParam(':before', $tracking->moneyBefore);
    $command->bindParam(':after', $tracking->moneyAfter);
    $command->bindParam(':click', $tracking->clickNum);
    $command->bindParam(':adStatus', $tracking->adStatus);
    $command->bindParam(':gridStatus', $tracking->gridStatus);
    $command->bindParam(':loginStatus', $tracking->loginStatus);
    $command->bindParam(':macAddress', $mac);

    $command->execute();

    return TRUE;
  }

  public function findByCondition($condition, $ofset = 0, $limit = Constants::RECORD_OF_PAGE) {
    $db = Yii::app()->db;
    $status = Constants::ACCOUNT_ACTIVE;

    $sql = 'SELECT at.account_id, at.tracking_date, at.money_before, '
         . 'at.money_after, at.click_num, at.ad_status, at.grid_status, '
         . 'at.login_status, at.mac_address, m.nickname, s.sitename, s.site_id '
         . 'FROM account_tracking AS at '
         . 'INNER JOIN accounts AS a ON at.account_id = a.account_id '
         . 'INNER JOIN members AS m ON a.member_id = m.member_id '
         . 'INNER JOIN sites AS s ON a.site_id = s.site_id '
         . 'WHERE m.status = :status AND s.status = :status ';

    if (isset($condition['fromDate'])) {
      $sql .= 'AND at.tracking_date >= :fromDate ';
    }

    if (isset($condition['toDate'])) {
      $sql .= 'AND at.tracking_date <= :toDate ';
    }

    if (isset($condition['siteId'])) {
      $sql .= 'AND s.site_id <= :siteId ';
    }

    if (isset($condition['nickname'])) {
      $sql .= 'AND m.nickname LIKE :nickname ';
    }

    if (isset($condition['memberId'])) {
      $sql .= 'AND m.member_id = :memberId ';
    }

    $sql .= 'ORDER BY tracking_date DESC, account_id ASC '
          . 'LIMIT ' . $ofset . ', ' . $limit;

    $command = $db->createCommand($sql);
    $command->bindParam(':status', $status);

    if (isset($condition['fromDate'])) {
      $command->bindParam(':fromDate', $condition['fromDate']);
    }

    if (isset($condition['toDate'])) {
      $command->bindParam(':toDate', $condition['toDate']);
    }

    if (isset($condition['siteId'])) {
      $command->bindParam(':siteId', $condition['siteId']);
    }

    if (isset($condition['nickname'])) {
      $command->bindParam(':nickname', $condition['nickname']);
    }

    if (isset($condition['memberId'])) {
      $command->bindParam(':memberId', $condition['memberId']);
    }

    $result = $command->queryAll();

    if ($result) {
        return $result;
    }

    return NULL;
  }

  public function countByCondition($condition) {
    $db = Yii::app()->db;
    $status = Constants::ACCOUNT_ACTIVE;

    $sql = 'SELECT COUNT(at.account_id) AS total '
         . 'FROM account_tracking AS at '
         . 'INNER JOIN accounts AS a ON at.account_id = a.account_id '
         . 'INNER JOIN members AS m ON a.member_id = m.member_id '
         . 'INNER JOIN sites AS s ON a.site_id = s.site_id '
         . 'WHERE m.status = :status AND s.status = :status ';

    if (isset($condition['fromDate'])) {
      $sql .= 'AND at.tracking_date >= :fromDate ';
    }

    if (isset($condition['toDate'])) {
      $sql .= 'AND at.tracking_date <= :toDate ';
    }

    if (isset($condition['siteId'])) {
      $sql .= 'AND s.site_id <= :siteId ';
    }

    if (isset($condition['nickname'])) {
      $sql .= 'AND m.nickname LIKE :nickname ';
    }

    if (isset($condition['memberId'])) {
        $sql .= 'AND m.member_id = :memberId ';
    }
    $command = $db->createCommand($sql);
    $command->bindParam(':status', $status);

    if (isset($condition['fromDate'])) {
      $command->bindParam(':fromDate', $condition['fromDate']);
    }

    if (isset($condition['toDate'])) {
      $command->bindParam(':toDate', $condition['toDate']);
    }

    if (isset($condition['siteId'])) {
      $command->bindParam(':siteId', $condition['siteId']);
    }

    if (isset($condition['nickname'])) {
      $command->bindParam(':nickname', $condition['nickname']);
    }

    if (isset($condition['memberId'])) {
        $command->bindParam(':memberId', $condition['memberId']);
    }

    $result = $command->queryAll();

    if ($result) {
        return $result[0]['total'];
    }

    return NULL;
  }
}